/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.IO;
using System.Xml;
using System.Web;
using System.Web.SessionState;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.ServiceModel.Activation;
using System.Web.Script.Serialization;
using System.Diagnostics;

namespace BzwayCRM
{
	// http://www.odata.org/developers/protocols/json-format
	// http://brennan.offwhite.net/blog/2008/10/21/simple-wcf-and-ajax-integration/
	[ServiceContract]
	[ServiceBehavior(IncludeExceptionDetailInFaults=true)]
	[AspNetCompatibilityRequirements(RequirementsMode=AspNetCompatibilityRequirementsMode.Required)]
	public class Rest
	{
		#region Scalar functions
		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public string Version()
		{
			// 03/10/2011 Paul.  We do not need to set the content type because the default is json. 
			//WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
			return Sql.ToString(HttpContext.Current.Application["SplendidVersion"]);
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public string Edition()
		{
			//WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
			return Sql.ToString(HttpContext.Current.Application["CONFIG.service_level"]);
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public DateTime UtcTime()
		{
			//WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
			return DateTime.UtcNow;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public bool IsAuthenticated()
		{
			//WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
			return Security.IsAuthenticated();
		}
		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Guid GetUserID()
		{
			if ( Security.IsAuthenticated() )
				return Security.USER_ID;
			else
				return Guid.Empty;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public string GetUserName()
		{
			if ( Security.IsAuthenticated() )
				return Security.USER_NAME;
			else
				return String.Empty;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Guid GetTeamID()
		{
			if ( Security.IsAuthenticated() )
				return Security.TEAM_ID;
			else
				return Guid.Empty;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public string GetTeamName()
		{
			if ( Security.IsAuthenticated() )
				return Security.TEAM_NAME;
			else
				return String.Empty;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public string GetUserLanguage()
		{
			if ( Security.IsAuthenticated() )
				return Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]);
			else
				return "en-US";
		}

		public class UserProfile
		{
			public Guid   USER_ID         ;
			public string USER_NAME       ;
			public Guid   TEAM_ID         ;
			public string TEAM_NAME       ;
			public string USER_LANG       ;
			public string USER_DATE_FORMAT;
			public string USER_TIME_FORMAT;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public UserProfile GetUserProfile()
		{
			if ( Security.IsAuthenticated() )
			{
				UserProfile profile = new UserProfile();
				profile.USER_ID          = Security.USER_ID  ;
				profile.USER_NAME        = Security.USER_NAME;
				profile.TEAM_ID          = Security.TEAM_ID  ;
				profile.TEAM_NAME        = Security.TEAM_NAME;
				profile.USER_LANG        = Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"   ]);
				profile.USER_DATE_FORMAT = Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/DATEFORMAT"]);
				profile.USER_TIME_FORMAT = Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/TIMEFORMAT"]);
				return profile;
			}
			else
			{
				L10N L10n = new L10N("en-US");
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
		}
		#endregion

		#region json utils
		// http://msdn.microsoft.com/en-us/library/system.datetime.ticks.aspx
		private static long UnixTicks(DateTime dt)
		{
			return (dt.Ticks - 621355968000000000) / 10000;
		}

		private static string ToJsonDate(object dt)
		{
			return "\\/Date(" + UnixTicks(Sql.ToDateTime(dt)).ToString() + ")\\/";
		}

		private static DateTime FromJsonDate(string s)
		{
			DateTime dt = DateTime.MinValue;
			if ( s.StartsWith("\\/Date(") && s.EndsWith(")\\/") )
			{
				s = s.Replace("\\/Date(", "");
				s = s.Replace(")\\/", "");
				long lEpoch = Sql.ToLong(s);
				dt = new DateTime(lEpoch * 10000 + 621355968000000000);
			}
			else
			{
				dt = Sql.ToDateTime(s);
			}
			return dt;
		}

		// http://schotime.net/blog/index.php/2008/07/27/dataset-datatable-to-json/
		private static List<Dictionary<string, object>> RowsToDictionary(string sBaseURI, string sModuleName, DataTable dt)
		{
			List<Dictionary<string, object>> objs = new List<Dictionary<string, object>>();
			foreach (DataRow dr in dt.Rows)
			{
				// 06/28/2011 Paul.  Now that we have switched to using views, the results may not have an ID column. 
				Dictionary<string, object> drow = new Dictionary<string, object>();
				if ( dt.Columns.Contains("ID") )
				{
					Guid gID = Sql.ToGuid(dr["ID"]);
					if ( !Sql.IsEmptyString(sBaseURI) && !Sql.IsEmptyString(sModuleName) )
					{
						Dictionary<string, object> metadata = new Dictionary<string, object>();
						metadata.Add("uri", sBaseURI + "?ModuleName=" + sModuleName + "&ID=" + gID.ToString() + "");
						metadata.Add("type", "SplendidCRM." + sModuleName);
						if ( dr.Table.Columns.Contains("DATE_MODIFIED_UTC") )
						{
							DateTime dtDATE_MODIFIED_UTC = Sql.ToDateTime(dr["DATE_MODIFIED_UTC"]);
							metadata.Add("etag", gID.ToString() + "." + dtDATE_MODIFIED_UTC.Ticks.ToString() );
						}
						drow.Add("__metadata", metadata);
					}
				}
				
				for (int i = 0; i < dt.Columns.Count; i++)
				{
					if ( dt.Columns[i].DataType.FullName == "System.DateTime" )
						drow.Add(dt.Columns[i].ColumnName, ToJsonDate(dr[i]) );
					else
						drow.Add(dt.Columns[i].ColumnName, dr[i]);
				}
				objs.Add(drow);
			}
			return objs;
		}

		private static Dictionary<string, object> ToJson(string sBaseURI, string sModuleName, DataTable dt)
		{
			Dictionary<string, object> d = new Dictionary<string, object>();
			Dictionary<string, object> results = new Dictionary<string, object>();
			results.Add("results", RowsToDictionary(sBaseURI, sModuleName, dt));
			d.Add("d", results);
			d.Add("__count", dt.Rows.Count.ToString());
			return d;
		}

		private static Dictionary<string, object> ToJson(string sBaseURI, string sModuleName, DataRow dr)
		{
			Dictionary<string, object> d       = new Dictionary<string, object>();
			Dictionary<string, object> results = new Dictionary<string, object>();
			Dictionary<string, object> drow    = new Dictionary<string, object>();
			
			// 06/28/2011 Paul.  Now that we have switched to using views, the results may not have an ID column. 
			if ( dr.Table.Columns.Contains("ID") )
			{
				Guid gID = Sql.ToGuid(dr["ID"]);
				if ( !Sql.IsEmptyString(sBaseURI) && !Sql.IsEmptyString(sModuleName) )
				{
					Dictionary<string, object> metadata = new Dictionary<string, object>();
					metadata.Add("uri", sBaseURI + "?ModuleName=" + sModuleName + "&ID=" + gID.ToString() + "");
					metadata.Add("type", "SplendidCRM." + sModuleName);
					if ( dr.Table.Columns.Contains("DATE_MODIFIED_UTC") )
					{
						DateTime dtDATE_MODIFIED_UTC = Sql.ToDateTime(dr["DATE_MODIFIED_UTC"]);
						metadata.Add("etag", gID.ToString() + "." + dtDATE_MODIFIED_UTC.Ticks.ToString() );
					}
					drow.Add("__metadata", metadata);
				}
			}
			
			for (int i = 0; i < dr.Table.Columns.Count; i++)
			{
				if ( dr.Table.Columns[i].DataType.FullName == "System.DateTime" )
					drow.Add(dr.Table.Columns[i].ColumnName, ToJsonDate(dr[i]) );
				else
					drow.Add(dr.Table.Columns[i].ColumnName, dr[i]);
			}
			
			results.Add("results", drow);
			d.Add("d", results);
			return d;
		}

		private static string ConvertODataFilter(string sFILTER, IDbCommand cmd)
		{
			// Logical Operators
			sFILTER = sFILTER.Replace(" eq true" , " eq 1");
			sFILTER = sFILTER.Replace(" eq false", " eq 0");
			sFILTER = sFILTER.Replace(" ne true" , " ne 1");
			sFILTER = sFILTER.Replace(" ne false", " ne 0");
			sFILTER = sFILTER.Replace(" gt ", " > ");
			sFILTER = sFILTER.Replace(" lt ", " < ");
			sFILTER = sFILTER.Replace(" eq ", " = ");
			sFILTER = sFILTER.Replace(" ne ", " <> ");
			// Arithmetic Operators
			sFILTER = sFILTER.Replace(" add ", " + ");
			sFILTER = sFILTER.Replace(" sub ", " - ");
			sFILTER = sFILTER.Replace(" mul ", " * ");
			sFILTER = sFILTER.Replace(" div ", " / ");
			sFILTER = sFILTER.Replace(" mod ", " % ");
			// Date Functions
			if ( Sql.IsSQLServer(cmd) )
			{
				//sFILTER = sFILTER.Replace("year("  , "dbo.fnDatePart('year', "  );
				//sFILTER = sFILTER.Replace("month(" , "dbo.fnDatePart('month', " );
				//sFILTER = sFILTER.Replace("day("   , "dbo.fnDatePart('day', "   );
				sFILTER = sFILTER.Replace("hour("  , "dbo.fnDatePart('hour', "  );
				sFILTER = sFILTER.Replace("minute(", "dbo.fnDatePart('minute', ");
				sFILTER = sFILTER.Replace("second(", "dbo.fnDatePart('second', ");
			}
			else
			{
				//sFILTER = sFILTER.Replace("year("  , "fnDatePart('year', "  );
				//sFILTER = sFILTER.Replace("month(" , "fnDatePart('month', " );
				//sFILTER = sFILTER.Replace("day("   , "fnDatePart('day', "   );
				sFILTER = sFILTER.Replace("hour("  , "fnDatePart('hour', "  );
				sFILTER = sFILTER.Replace("minute(", "fnDatePart('minute', ");
				sFILTER = sFILTER.Replace("second(", "fnDatePart('second', ");
			}
			// Math Functions
			int nStart = sFILTER.IndexOf("round(");
			while ( nStart > 0 )
			{
				int nEnd = sFILTER.IndexOf(")", nStart);
				if ( nEnd > 0 )
				{
					sFILTER = sFILTER.Substring(0, nEnd - 1) + ", 0" + sFILTER.Substring(nEnd - 1);
				}
				nStart = sFILTER.IndexOf("round(", nStart + 1);
			}
			// String Functions
			sFILTER = sFILTER.Replace("tolower(", "lower(");
			sFILTER = sFILTER.Replace("toupper(", "upper(");
			if ( Sql.IsSQLServer(cmd) )
			{
				sFILTER = sFILTER.Replace("length("     , "len(");
				sFILTER = sFILTER.Replace("trim("       , "dbo.fnTrim(");
				sFILTER = sFILTER.Replace("concat("     , "dbo.fnConcat(");
				sFILTER = sFILTER.Replace("startswith(" , "dbo.fnStartsWith(");
				sFILTER = sFILTER.Replace("endswith("   , "dbo.fnEndsWith(");
				sFILTER = sFILTER.Replace("indexof("    , "dbo.fnIndexOf(");
				sFILTER = sFILTER.Replace("substringof(", "dbo.fnSubstringOf(");
			}
			return sFILTER;
		}
		#endregion

		#region Login
		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Guid Login(string UserName, string Password, string Version)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpSessionState     Session     = HttpContext.Current.Session    ;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			string sUSER_NAME   = UserName;
			string sPASSWORD    = Password;
			string sVERSION     = Version ;
			Guid gUSER_ID       = Guid.Empty;
			Guid gUSER_LOGIN_ID = Guid.Empty;
			
			// 02/23/2011 Paul.  SYNC service should check for lockout. 
			if ( SplendidInit.LoginFailures(Application, sUSER_NAME) >= Crm.Password.LoginLockoutCount(Application) )
			{
				L10N L10n = new L10N("en-US");
				throw(new Exception(L10n.Term("Users.ERR_USER_LOCKED_OUT")));
			}
			DbProviderFactory dbf = DbProviderFactories.GetFactory();
			using ( IDbConnection con = dbf.CreateConnection() )
			{
				con.Open();
				string sSQL;
				sSQL = "select ID                    " + ControlChars.CrLf
				     + "     , USER_NAME             " + ControlChars.CrLf
				     + "     , FULL_NAME             " + ControlChars.CrLf
				     + "     , IS_ADMIN              " + ControlChars.CrLf
				     + "     , STATUS                " + ControlChars.CrLf
				     + "     , PORTAL_ONLY           " + ControlChars.CrLf
				     + "     , TEAM_ID               " + ControlChars.CrLf
				     + "     , TEAM_NAME             " + ControlChars.CrLf
				     + "  from vwUSERS_Login         " + ControlChars.CrLf
				     + " where USER_NAME = @USER_NAME" + ControlChars.CrLf
				     + "   and USER_HASH = @USER_HASH" + ControlChars.CrLf;
				using ( IDbCommand cmd = con.CreateCommand() )
				{
					cmd.CommandText = sSQL;
					string sUSER_HASH = Security.HashPassword(sPASSWORD);
					// 12/25/2009 Paul.  Use lowercase username to match the primary authentication function. 
					Sql.AddParameter(cmd, "@USER_NAME", sUSER_NAME.ToLower());
					Sql.AddParameter(cmd, "@USER_HASH", sUSER_HASH);
					using ( DbDataAdapter da = dbf.CreateDataAdapter() )
					{
						((IDbDataAdapter)da).SelectCommand = cmd;
						using ( DataTable dt = new DataTable() )
						{
							da.Fill(dt);
							if ( dt.Rows.Count > 0 )
							{
								DataRow row = dt.Rows[0];
								Security.USER_ID     = Sql.ToGuid   (row["ID"         ]);
								Security.USER_NAME   = Sql.ToString (row["USER_NAME"  ]);
								Security.FULL_NAME   = Sql.ToString (row["FULL_NAME"  ]);
								Security.IS_ADMIN    = Sql.ToBoolean(row["IS_ADMIN"   ]);
								Security.PORTAL_ONLY = Sql.ToBoolean(row["PORTAL_ONLY"]);
								Security.TEAM_ID     = Sql.ToGuid   (row["TEAM_ID"    ]);
								Security.TEAM_NAME   = Sql.ToString (row["TEAM_NAME"  ]);
								gUSER_ID = Sql.ToGuid(row["ID"]);

								SplendidInit.LoadUserPreferences(gUSER_ID, String.Empty, String.Empty);
								SplendidInit.LoadUserACL(gUSER_ID);
								
								SqlProcs.spUSERS_LOGINS_InsertOnly(ref gUSER_LOGIN_ID, gUSER_ID, sUSER_NAME, "Anonymous", "Succeeded", Session.SessionID, Request.UserHostName, Request.Url.Host, Request.Path, Request.AppRelativeCurrentExecutionFilePath, Request.UserAgent);
								Security.USER_LOGIN_ID = gUSER_LOGIN_ID;
								// 02/20/2011 Paul.  Log the success so that we can lockout the user. 
								SplendidInit.LoginTracking(Application, sUSER_NAME, true);
								SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "SyncUser login for " + sUSER_NAME);
							}
							else
							{
								SqlProcs.spUSERS_LOGINS_InsertOnly(ref gUSER_LOGIN_ID, Guid.Empty, sUSER_NAME, "Anonymous", "Failed", Session.SessionID, Request.UserHostName, Request.Url.Host, Request.Path, Request.AppRelativeCurrentExecutionFilePath, Request.UserAgent);
								// 02/20/2011 Paul.  Log the failure so that we can lockout the user. 
								SplendidInit.LoginTracking(Application, sUSER_NAME, false);
								SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "SECURITY: failed attempted login for " + sUSER_NAME + " using Sync api");
							}
						}
					}
				}
			}
			if ( gUSER_ID == Guid.Empty )
			{
				SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Invalid username and/or password for " + sUSER_NAME);
				throw(new Exception("Invalid username and/or password for " + sUSER_NAME));
			}
			return gUSER_ID;
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public void Logout()
		{
			try
			{
				Guid gUSER_LOGIN_ID = Security.USER_LOGIN_ID;
				if ( !Sql.IsEmptyGuid(gUSER_LOGIN_ID) )
					SqlProcs.spUSERS_LOGINS_Logout(gUSER_LOGIN_ID);
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
			}
			HttpContext.Current.Session.Abandon();
		}
		#endregion

		#region Get
		// 10/16/2011 Paul.  HTML5 Offline Client needs access to the custom lists. 
		[OperationContract]
		[WebInvoke(Method="GET", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Stream GetCustomList(string ListName)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control", "no-cache");
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma", "no-cache");
			
			if ( Sql.IsEmptyString(ListName) )
				throw(new Exception("The list name must be specified."));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			if ( !Security.IsAuthenticated() )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			DataTable dt = new DataTable();
			dt.Columns.Add("NAME"        );
			dt.Columns.Add("DISPLAY_NAME");
			bool bCustomCache = false;
			SplendidCacheReference[] arrCustomCaches = SplendidCache.CustomCaches;
			foreach ( SplendidCacheReference cache in arrCustomCaches )
			{
				if ( cache.Name == ListName )
				{
					string sDataValueField = cache.DataValueField;
					string sDataTextField  = cache.DataTextField ;
					SplendidCacheCallback cbkDataSource = cache.DataSource;
					foreach ( DataRow rowCustom in cbkDataSource().Rows )
					{
						DataRow row = dt.NewRow();
						dt.Rows.Add(row);
						row["NAME"        ] = Sql.ToString(rowCustom[sDataValueField]);
						row["DISPLAY_NAME"] = Sql.ToString(rowCustom[sDataTextField ]);
					}
					bCustomCache = true;
				}
			}
			if ( !bCustomCache )
			{
				dt = SplendidCache.List(ListName);
			}
			string sBaseURI = Request.Url.Scheme + "://" + Request.Url.Host + Request.Url.AbsolutePath;
			JavaScriptSerializer json = new JavaScriptSerializer();
			json.MaxJsonLength = 20 * 1024 * 1024;
			string sResponse = json.Serialize(ToJson(sBaseURI, ListName, dt));
			byte[] byResponse = Encoding.UTF8.GetBytes(sResponse);
			return new MemoryStream(byResponse);
		}

		[OperationContract]
		[WebInvoke(Method="GET", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Stream GetModuleTable(string TableName)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control", "no-cache");
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma", "no-cache");
			
			int    nSKIP     = Sql.ToInteger(Request.QueryString["$skip"   ]);
			int    nTOP      = Sql.ToInteger(Request.QueryString["$top"    ]);
			string sFILTER   = Sql.ToString (Request.QueryString["$filter" ]);
			string sORDER_BY = Sql.ToString (Request.QueryString["$orderby"]);
			// 08/03/2011 Paul.  We need a way to filter the columns so that we can be efficient. 
			string sSELECT   = Sql.ToString (Request.QueryString["$select" ]);
			string[] arrItems = Request.QueryString.GetValues("Items");
			Guid[] Items = null;
			// 06/17/2011 Paul.  arrItems might be null. 
			if ( arrItems != null && arrItems.Length > 0 )
			{
				Items = new Guid[arrItems.Length];
				for ( int i = 0; i < arrItems.Length; i++ )
				{
					Items[i] = Sql.ToGuid(arrItems[i]);
				}
			}
			Regex r = new Regex(@"[^A-Za-z0-9_]");
			string sFILTER_KEYWORDS = (" " + r.Replace(sFILTER, " ") + " ").ToLower();
			if ( sFILTER_KEYWORDS.Contains(" select ") )
			{
				throw(new Exception("Subqueries are not allowed."));
			}
			if ( sFILTER.Contains(";") )
			{
				// 06/18/2011 Paul.  This is to prevent the user from attempting to inject SQL. 
				throw(new Exception("A semicolon is not allowed anywhere in a filter. "));
			}
			if ( sORDER_BY.Contains(";") )
			{
				// 06/18/2011 Paul.  This is to prevent the user from attempting to inject SQL. 
				throw(new Exception("A semicolon is not allowed anywhere in a sort expression. "));
			}
			if ( !Security.IsAuthenticated() )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			// 08/22/2011 Paul.  Add admin control to REST API. 
			string sMODULE_NAME = Sql.ToString(Application["Modules." + TableName + ".ModuleName"]);
			// 08/22/2011 Paul.  Not all tables will have a module name, such as relationship tables. 
			// Tables will get another security filter later in the code. 
			if ( !Sql.IsEmptyString(sMODULE_NAME) )
			{
				int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, "list");
				if ( !Sql.ToBoolean(Application["Modules." + sMODULE_NAME + ".RestEnabled"]) || nACLACCESS < 0 )
				{
					L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
					throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
				}
			}
			
			UniqueStringCollection arrSELECT = new UniqueStringCollection();
			sSELECT = sSELECT.Replace(" ", "");
			if ( !Sql.IsEmptyString(sSELECT) )
			{
				foreach ( string s in sSELECT.Split(',') )
				{
					string sColumnName = r.Replace(s, "");
					if ( !Sql.IsEmptyString(sColumnName) )
						arrSELECT.Add(sColumnName);
				}
			}
			
			DataTable dt = GetTable(TableName, nSKIP, nTOP, sFILTER, sORDER_BY, arrSELECT, Items);
			
			string sBaseURI = Request.Url.Scheme + "://" + Request.Url.Host + Request.Url.AbsolutePath.Replace("/GetModuleTable", "/GetModuleItem");
			JavaScriptSerializer json = new JavaScriptSerializer();
			json.MaxJsonLength = 20 * 1024 * 1024;
			string sResponse = json.Serialize(ToJson(sBaseURI, sMODULE_NAME, dt));
			byte[] byResponse = Encoding.UTF8.GetBytes(sResponse);
			return new MemoryStream(byResponse);
		}

		[OperationContract]
		[WebInvoke(Method="GET", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Stream GetModuleList(string ModuleName)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control", "no-cache");
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma", "no-cache");
			
			if ( Sql.IsEmptyString(ModuleName) )
				throw(new Exception("The module name must be specified."));
			string sTABLE_NAME = Sql.ToString(Application["Modules." + ModuleName + ".TableName"]);
			if ( Sql.IsEmptyString(sTABLE_NAME) )
				throw(new Exception("Unknown module: " + ModuleName));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			int nACLACCESS = Security.GetUserAccess(ModuleName, "list");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + ModuleName + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			int    nSKIP     = Sql.ToInteger(Request.QueryString["$skip"   ]);
			int    nTOP      = Sql.ToInteger(Request.QueryString["$top"    ]);
			string sFILTER   = Sql.ToString (Request.QueryString["$filter" ]);
			string sORDER_BY = Sql.ToString (Request.QueryString["$orderby"]);
			// 08/03/2011 Paul.  We need a way to filter the columns so that we can be efficient. 
			string sSELECT   = Sql.ToString (Request.QueryString["$select" ]);
			
			Regex r = new Regex(@"[^A-Za-z0-9_]");
			string sFILTER_KEYWORDS = (" " + r.Replace(sFILTER, " ") + " ").ToLower();
			if ( sFILTER_KEYWORDS.Contains(" select ") )
				throw(new Exception("Subqueries are not allowed."));

			UniqueStringCollection arrSELECT = new UniqueStringCollection();
			sSELECT = sSELECT.Replace(" ", "");
			if ( !Sql.IsEmptyString(sSELECT) )
			{
				foreach ( string s in sSELECT.Split(',') )
				{
					string sColumnName = r.Replace(s, "");
					if ( !Sql.IsEmptyString(sColumnName) )
						arrSELECT.Add(sColumnName);
				}
			}
			
			DataTable dt = GetTable(sTABLE_NAME, nSKIP, nTOP, sFILTER, sORDER_BY, arrSELECT, null);
			
			string sBaseURI = Request.Url.Scheme + "://" + Request.Url.Host + Request.Url.AbsolutePath.Replace("/GetModuleList", "/GetModuleItem");
			JavaScriptSerializer json = new JavaScriptSerializer();
			json.MaxJsonLength = 20 * 1024 * 1024;
			string sResponse = json.Serialize(ToJson(sBaseURI, ModuleName, dt));
			byte[] byResponse = Encoding.UTF8.GetBytes(sResponse);
			return new MemoryStream(byResponse);
		}

		[OperationContract]
		[WebInvoke(Method="GET", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public Stream GetModuleItem(string ModuleName, Guid ID)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control", "no-cache");
			WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma", "no-cache");
			
			if ( Sql.IsEmptyString(ModuleName) )
				throw(new Exception("The module name must be specified."));
			string sTABLE_NAME = Sql.ToString(Application["Modules." + ModuleName + ".TableName"]);
			if ( Sql.IsEmptyString(sTABLE_NAME) )
				throw(new Exception("Unknown module: " + ModuleName));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			int nACLACCESS = Security.GetUserAccess(ModuleName, "view");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + ModuleName + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			Guid[] arrITEMS = new Guid[1] { ID };
			DataTable dt = GetTable(sTABLE_NAME, 0, 1, String.Empty, String.Empty, null, arrITEMS);
			if ( dt == null || dt.Rows.Count == 0 )
				throw(new Exception("Item not found: " + ModuleName + " " + ID.ToString()));
			
			string sBaseURI = Request.Url.Scheme + "://" + Request.Url.Host + Request.Url.AbsolutePath;
			JavaScriptSerializer json = new JavaScriptSerializer();
			
			Dictionary<string, object> dict = ToJson(sBaseURI, ModuleName, dt.Rows[0]);
			
			string sEXPAND = Sql.ToString (Request.QueryString["$expand"]);
			if ( sEXPAND == "*" )
			{
				DbProviderFactory dbf = DbProviderFactories.GetFactory();
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					con.Open();
					Dictionary<string, object> d       = dict["d"] as Dictionary<string, object>;
					Dictionary<string, object> results = d["results"] as Dictionary<string, object>;
					DataTable dtRelationships = SplendidCache.DetailViewRelationships(ModuleName + ".DetailView");
					foreach ( DataRow row in dtRelationships.Rows )
					{
						try
						{
							string sRELATED_MODULE     = Sql.ToString(row["MODULE_NAME"]);
							string sRELATED_TABLE      = Sql.ToString(Application["Modules." + sRELATED_MODULE + ".TableName"]);
							string sRELATED_FIELD_NAME = Crm.Modules.SingularTableName(sRELATED_TABLE) + "_ID";
							if ( !d.ContainsKey(sRELATED_MODULE) && BzwayCRM.Security.GetUserAccess(sRELATED_MODULE, "list") >= 0 )
							{
								using ( DataTable dtSYNC_TABLES = SplendidCache.RestTables(sRELATED_TABLE, true) )
								{
									string sSQL;
									if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count > 0 )
									{
										UniqueStringCollection arrSearchFields = new UniqueStringCollection();
										SplendidDynamic.SearchGridColumns(ModuleName + "." + sRELATED_MODULE, arrSearchFields);
										
										sSQL = "select " + Sql.FormatSelectFields(arrSearchFields)
										     + "  from vw" + sTABLE_NAME + "_" + sRELATED_TABLE + ControlChars.CrLf;
										using ( IDbCommand cmd = con.CreateCommand() )
										{
											cmd.CommandText = sSQL;
											Security.Filter(cmd, sRELATED_MODULE, "list");
											Sql.AppendParameter(cmd, ID, sRELATED_FIELD_NAME);
											using ( DbDataAdapter da = dbf.CreateDataAdapter() )
											{
												((IDbDataAdapter)da).SelectCommand = cmd;
												using ( DataTable dtSubPanel = new DataTable() )
												{
													da.Fill(dtSubPanel);
													results.Add(sRELATED_MODULE, RowsToDictionary(sBaseURI, sRELATED_MODULE, dtSubPanel));
												}
											}
										}
									}
								}
							}
						}
						catch(Exception ex)
						{
							SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
						}
					}
				}
			}
			
			string sResponse = json.Serialize(dict);
			byte[] byResponse = Encoding.UTF8.GetBytes(sResponse);
			return new MemoryStream(byResponse);
		}

		private DataTable GetTable(string sTABLE_NAME, int nSKIP, int nTOP, string sFILTER, string sORDER_BY, UniqueStringCollection arrSELECT, Guid[] arrITEMS)
		{
			HttpContext      Context = HttpContext.Current;
			HttpSessionState Session = HttpContext.Current.Session;
			DataTable dt = null;
			try
			{
				// 09/03/2011 Paul.  We should use the cached layout tables instead of a database lookup for performance reasons. 
				// When getting the layout tables, we typically only need the view name, so extract from the filter string. 
				// The Regex match will allow an OData query. 
				if ( Security.IsAuthenticated() )
				{
					string sMATCH_NAME = String.Empty;
					if ( sTABLE_NAME == "DYNAMIC_BUTTONS" )
					{
						sMATCH_NAME = "VIEW_NAME";
						Match match = Regex.Match(sFILTER, "\\b" + sMATCH_NAME + "\\s*(=|eq)\\s*\'(?<" + sMATCH_NAME + ">([^(\'|\\s)]*))", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase);
						if ( match.Success )
						{
							string sVIEW_NAME = match.Groups[sMATCH_NAME].Value;
							dt = SplendidCache.DynamicButtons(sVIEW_NAME).Copy();
							if ( dt != null )
							{
								bool bRowsDeleted = false;
								foreach(DataRow row in dt.Rows)
								{
									string sCONTROL_TYPE       = Sql.ToString (row["CONTROL_TYPE"      ]);
									string sMODULE_NAME        = Sql.ToString (row["MODULE_NAME"       ]);
									string sMODULE_ACCESS_TYPE = Sql.ToString (row["MODULE_ACCESS_TYPE"]);
									string sTARGET_NAME        = Sql.ToString (row["TARGET_NAME"       ]);
									string sTARGET_ACCESS_TYPE = Sql.ToString (row["TARGET_ACCESS_TYPE"]);
									bool   bADMIN_ONLY         = Sql.ToBoolean(row["ADMIN_ONLY"        ]);
									
									bool bVisible = (bADMIN_ONLY && Security.IS_ADMIN || !bADMIN_ONLY);
									if ( String.Compare(sCONTROL_TYPE, "Button", true) == 0 || String.Compare(sCONTROL_TYPE, "HyperLink", true) == 0 || String.Compare(sCONTROL_TYPE, "ButtonLink", true) == 0 )
									{
										if ( bVisible && !Sql.IsEmptyString(sMODULE_NAME) && !Sql.IsEmptyString(sMODULE_ACCESS_TYPE) )
										{
											int nACLACCESS = BzwayCRM.Security.GetUserAccess(sMODULE_NAME, sMODULE_ACCESS_TYPE);
											// 09/03/2011 Paul.  Can't apply Owner rights without the item record. 
											//bVisible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && ((Security.USER_ID == gASSIGNED_USER_ID) || (!bIsPostBack && rdr == null) || (rdr != null && bShowUnassigned && Sql.IsEmptyGuid(gASSIGNED_USER_ID))));
											if ( bVisible && !Sql.IsEmptyString(sTARGET_NAME) && !Sql.IsEmptyString(sTARGET_ACCESS_TYPE) )
											{
												nACLACCESS = BzwayCRM.Security.GetUserAccess(sTARGET_NAME, sTARGET_ACCESS_TYPE);
												// 09/03/2011 Paul.  Can't apply Owner rights without the item record. 
												//bVisible = (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && ((Security.USER_ID == gASSIGNED_USER_ID) || (!bIsPostBack && rdr == null) || (rdr != null && bShowUnassigned && Sql.IsEmptyGuid(gASSIGNED_USER_ID))));
											}
										}
									}
									if ( !bVisible )
									{
										row.Delete();
										bRowsDeleted = true;
									}
								}
								if ( bRowsDeleted )
									dt.AcceptChanges();
							}
							return dt;
						}
					}
					else if ( sTABLE_NAME == "GRIDVIEWS_COLUMNS" )
					{
						sMATCH_NAME = "GRID_NAME";
						Match match = Regex.Match(sFILTER, "\\b" + sMATCH_NAME + "\\s*(=|eq)\\s*\'(?<" + sMATCH_NAME + ">([^(\'|\\s)]*))", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase);
						if ( match.Success )
						{
							string sGRID_NAME = match.Groups[sMATCH_NAME].Value;
							dt = SplendidCache.GridViewColumns(sGRID_NAME);
							// 09/03/2011 Paul.  Apply Field Level Security before sending to the client. 
							if ( dt != null && SplendidInit.bEnableACLFieldSecurity )
							{
								bool bRowsDeleted = false;
								foreach(DataRow row in dt.Rows)
								{
									string sDATA_FIELD  = Sql.ToString (row["DATA_FIELD"]);
									string sMODULE_NAME = String.Empty;
									string[] arrGRID_NAME = sGRID_NAME.Split('.');
									if ( arrGRID_NAME.Length > 0 )
									{
										if ( arrGRID_NAME[0] == "ListView" || arrGRID_NAME[0] == "PopupView" || arrGRID_NAME[0] == "Activities" )
											sMODULE_NAME = arrGRID_NAME[0];
										else if ( Sql.ToBoolean(HttpContext.Current.Application["Modules." + arrGRID_NAME[1] + ".Valid"]) )
											sMODULE_NAME = arrGRID_NAME[1];
										else
											sMODULE_NAME = arrGRID_NAME[0];
									}
									bool bIsReadable = true;
									if ( SplendidInit.bEnableACLFieldSecurity && !Sql.IsEmptyString(sDATA_FIELD) )
									{
										Security.ACL_FIELD_ACCESS acl = Security.GetUserFieldSecurity(sMODULE_NAME, sDATA_FIELD, Guid.Empty);
										bIsReadable  = acl.IsReadable();
									}
									if ( !bIsReadable )
									{
										row.Delete();
										bRowsDeleted = true;
									}
								}
								if ( bRowsDeleted )
									dt.AcceptChanges();
							}
							return dt;
						}
					}
					else if ( sTABLE_NAME == "EDITVIEWS_FIELDS" )
					{
						sMATCH_NAME = "EDIT_NAME";
						Match match = Regex.Match(sFILTER, "\\b" + sMATCH_NAME + "\\s*(=|eq)\\s*\'(?<" + sMATCH_NAME + ">([^(\'|\\s)]*))", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase);
						if ( match.Success )
						{
							string sEDIT_NAME = match.Groups[sMATCH_NAME].Value;
							dt = SplendidCache.EditViewFields(sEDIT_NAME);
							// 09/03/2011 Paul.  Apply Field Level Security before sending to the client. 
							if ( dt != null && SplendidInit.bEnableACLFieldSecurity )
							{
								foreach(DataRow row in dt.Rows)
								{
									string sFIELD_TYPE    = Sql.ToString (row["FIELD_TYPE"   ]);
									string sDATA_FIELD    = Sql.ToString (row["DATA_FIELD"   ]);
									string sDATA_FORMAT   = Sql.ToString (row["DATA_FORMAT"  ]);
									string sDISPLAY_FIELD = Sql.ToString (row["DISPLAY_FIELD"]);
									string sMODULE_NAME   = String.Empty;
									string[] arrEDIT_NAME = sEDIT_NAME.Split('.');
									if ( arrEDIT_NAME.Length > 0 )
										sMODULE_NAME = arrEDIT_NAME[0];
									bool bIsReadable  = true;
									bool bIsWriteable = true;
									if ( SplendidInit.bEnableACLFieldSecurity )
									{
										// 09/03/2011 Paul.  Can't apply Owner rights without the item record. 
										Security.ACL_FIELD_ACCESS acl = Security.GetUserFieldSecurity(sMODULE_NAME, sDATA_FIELD, Guid.Empty);
										bIsReadable  = acl.IsReadable();
										// 02/16/2011 Paul.  We should allow a Read-Only field to be searchable, so always allow writing if the name contains Search. 
										bIsWriteable = acl.IsWriteable() || sEDIT_NAME.Contains(".Search");
									}
									if ( !bIsReadable )
									{
										row["FIELD_TYPE"] = "Blank";
									}
									else if ( !bIsWriteable )
									{
										row["FIELD_TYPE"] = "Label";
									}
								}
							}
							return dt;
						}
					}
					else if ( sTABLE_NAME == "DETAILVIEWS_FIELDS" )
					{
						sMATCH_NAME = "DETAIL_NAME";
						Match match = Regex.Match(sFILTER, "\\b" + sMATCH_NAME + "\\s*(=|eq)\\s*\'(?<" + sMATCH_NAME + ">([^(\'|\\s)]*))", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase);
						if ( match.Success )
						{
							string sDETAIL_NAME = match.Groups[sMATCH_NAME].Value;
							dt = SplendidCache.DetailViewFields(sDETAIL_NAME);
							// 09/03/2011 Paul.  Apply Field Level Security before sending to the client. 
							if ( dt != null && SplendidInit.bEnableACLFieldSecurity )
							{
								foreach(DataRow row in dt.Rows)
								{
									string sDATA_FIELD  = Sql.ToString (row["DATA_FIELD"]);
									string sMODULE_NAME = String.Empty;
									string[] arrDETAIL_NAME = sDETAIL_NAME.Split('.');
									if ( arrDETAIL_NAME.Length > 0 )
										sMODULE_NAME = arrDETAIL_NAME[0];
									bool bIsReadable  = true;
									if ( SplendidInit.bEnableACLFieldSecurity && !Sql.IsEmptyString(sDATA_FIELD) )
									{
										// 09/03/2011 Paul.  Can't apply Owner rights without the item record. 
										Security.ACL_FIELD_ACCESS acl = Security.GetUserFieldSecurity(sMODULE_NAME, sDATA_FIELD, Guid.Empty);
										bIsReadable  = acl.IsReadable();
									}
									if ( !bIsReadable )
									{
										row["FIELD_TYPE"] = "Blank";
									}
								}
							}
							return dt;
						}
					}
					else if ( sTABLE_NAME == "DETAILVIEWS_RELATIONSHIPS" )
					{
						sMATCH_NAME = "DETAIL_NAME";
						Match match = Regex.Match(sFILTER, "\\b" + sMATCH_NAME + "\\s*(=|eq)\\s*\'(?<" + sMATCH_NAME + ">([^(\'|\\s)]*))", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase);
						if ( match.Success )
						{
							string sVIEW_NAME = match.Groups[sMATCH_NAME].Value;
							dt = SplendidCache.DetailViewRelationships(sVIEW_NAME).Copy();
							if ( dt != null )
							{
								bool bRowsDeleted = false;
								foreach(DataRow row in dt.Rows)
								{
									string sMODULE_NAME  = Sql.ToString (row["MODULE_NAME" ]);
									string sCONTROL_NAME = Sql.ToString (row["CONTROL_NAME"]);
									
									bool bVisible = (BzwayCRM.Security.GetUserAccess(sMODULE_NAME, "list") >= 0);
									if ( !bVisible )
									{
										row.Delete();
										bRowsDeleted = true;
									}
								}
								if ( bRowsDeleted )
									dt.AcceptChanges();
							}
							return dt;
						}
					}
					else if ( sTABLE_NAME == "TAB_MENUS" )
					{
						dt = SplendidCache.TabMenu();
						return dt;
					}
					Regex r = new Regex(@"[^A-Za-z0-9_]");
					sTABLE_NAME = r.Replace(sTABLE_NAME, "");
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						con.Open();
						// 06/03/2011 Paul.  Cache the Rest Table data. 
						using ( DataTable dtSYNC_TABLES = SplendidCache.RestTables(sTABLE_NAME, false) )
						{
							string sSQL = String.Empty;
							if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count > 0 )
							{
								DataRow rowSYNC_TABLE = dtSYNC_TABLES.Rows[0];
								string sMODULE_NAME         = Sql.ToString (rowSYNC_TABLE["MODULE_NAME"        ]);
								string sVIEW_NAME           = Sql.ToString (rowSYNC_TABLE["VIEW_NAME"          ]);
								bool   bHAS_CUSTOM          = Sql.ToBoolean(rowSYNC_TABLE["HAS_CUSTOM"         ]);
								int    nMODULE_SPECIFIC     = Sql.ToInteger(rowSYNC_TABLE["MODULE_SPECIFIC"    ]);
								string sMODULE_FIELD_NAME   = Sql.ToString (rowSYNC_TABLE["MODULE_FIELD_NAME"  ]);
								bool   bIS_RELATIONSHIP     = Sql.ToBoolean(rowSYNC_TABLE["IS_RELATIONSHIP"    ]);
								string sMODULE_NAME_RELATED = Sql.ToString (rowSYNC_TABLE["MODULE_NAME_RELATED"]);
								string sASSIGNED_FIELD_NAME = Sql.ToString (rowSYNC_TABLE["ASSIGNED_FIELD_NAME"]);
								// 09/28/2011 Paul.  Include the system flag so that we can cache only system tables. 
								bool   bIS_SYSTEM           = Sql.ToBoolean(rowSYNC_TABLE["IS_SYSTEM"          ]);
								// 11/01/2009 Paul.  Protect against SQL Injection. A table name will never have a space character.
								sTABLE_NAME                 = Sql.ToString (rowSYNC_TABLE["TABLE_NAME"         ]);
								sTABLE_NAME        = r.Replace(sTABLE_NAME       , "");
								sVIEW_NAME         = r.Replace(sVIEW_NAME        , "");
								sMODULE_FIELD_NAME = r.Replace(sMODULE_FIELD_NAME, "");
								
								// 09/28/2011 Paul.  Non-system tables should not be cached on the server because they can change at any time. 
								// 10/01/2011 Paul.  We are getting No Response on system tables and no network request is made when online. 
								//if ( !bIS_SYSTEM )
									HttpContext.Current.Response.ExpiresAbsolute = new DateTime(1980, 1, 1, 0, 0, 0, 0);
								
								// 08/03/2011 Paul.  We need a way to filter the columns so that we can be efficient. 
								if ( arrSELECT != null && arrSELECT.Count > 0 )
								{
									foreach ( string sColumnName in arrSELECT )
									{
										if ( Sql.IsEmptyString(sSQL) )
											sSQL += "select " + sVIEW_NAME + "." + sColumnName + ControlChars.CrLf;
										else
											sSQL += "     , " + sVIEW_NAME + "." + sColumnName + ControlChars.CrLf;
									}
								}
								else
								{
									sSQL = "select " + sVIEW_NAME + ".*" + ControlChars.CrLf;
								}
								// 06/18/2011 Paul.  The REST API tables will use the view properly, so there is no need to join to the CSTM table. 
								sSQL += "  from " + sVIEW_NAME        + ControlChars.CrLf;
								using ( IDbCommand cmd = con.CreateCommand() )
								{
									cmd.CommandText = sSQL;
									cmd.CommandTimeout = 0;
									// 10/27/2009 Paul.  Apply the standard filters. 
									// 11/03/2009 Paul.  Relationship tables will not have Team or Assigned fields. 
									if ( bIS_RELATIONSHIP )
									{
										cmd.CommandText += " where 1 = 1" + ControlChars.CrLf;
										// 11/06/2009 Paul.  Use the relationship table to get the module information. 
										DataView vwRelationships = new DataView(SplendidCache.ReportingRelationships(Context.Application));
										vwRelationships.RowFilter = "(JOIN_TABLE = '" + sTABLE_NAME + "' and RELATIONSHIP_TYPE = 'many-to-many') or (RHS_TABLE = '" + sTABLE_NAME + "' and RELATIONSHIP_TYPE = 'one-to-many')";
										if ( vwRelationships.Count > 0 )
										{
											foreach ( DataRowView rowRelationship in vwRelationships )
											{
												string sJOIN_KEY_LHS             = Sql.ToString(rowRelationship["JOIN_KEY_LHS"            ]).ToUpper();
												string sJOIN_KEY_RHS             = Sql.ToString(rowRelationship["JOIN_KEY_RHS"            ]).ToUpper();
												string sLHS_MODULE               = Sql.ToString(rowRelationship["LHS_MODULE"              ]);
												string sRHS_MODULE               = Sql.ToString(rowRelationship["RHS_MODULE"              ]);
												string sLHS_TABLE                = Sql.ToString(rowRelationship["LHS_TABLE"               ]).ToUpper();
												string sRHS_TABLE                = Sql.ToString(rowRelationship["RHS_TABLE"               ]).ToUpper();
												string sLHS_KEY                  = Sql.ToString(rowRelationship["LHS_KEY"                 ]).ToUpper();
												string sRHS_KEY                  = Sql.ToString(rowRelationship["RHS_KEY"                 ]).ToUpper();
												string sRELATIONSHIP_TYPE        = Sql.ToString(rowRelationship["RELATIONSHIP_TYPE"       ]);
												string sRELATIONSHIP_ROLE_COLUMN = Sql.ToString(rowRelationship["RELATIONSHIP_ROLE_COLUMN"]).ToUpper();
												sJOIN_KEY_LHS = r.Replace(sJOIN_KEY_LHS, "");
												sJOIN_KEY_RHS = r.Replace(sJOIN_KEY_RHS, "");
												sLHS_MODULE   = r.Replace(sLHS_MODULE  , "");
												sRHS_MODULE   = r.Replace(sRHS_MODULE  , "");
												sLHS_TABLE    = r.Replace(sLHS_TABLE   , "");
												sRHS_TABLE    = r.Replace(sRHS_TABLE   , "");
												sLHS_KEY      = r.Replace(sLHS_KEY     , "");
												sRHS_KEY      = r.Replace(sRHS_KEY     , "");
												if ( sRELATIONSHIP_TYPE == "many-to-many" )
												{
													cmd.CommandText += "   and " + sJOIN_KEY_LHS + " in " + ControlChars.CrLf;
													cmd.CommandText += "(select " + sLHS_KEY + " from " + sLHS_TABLE + ControlChars.CrLf;
													Security.Filter(cmd, sLHS_MODULE, "list");
													cmd.CommandText += ")" + ControlChars.CrLf;
													
													// 11/12/2009 Paul.  We don't want to deal with relationships to multiple tables, so just ignore for now. 
													if ( sRELATIONSHIP_ROLE_COLUMN != "RELATED_TYPE" )
													{
														cmd.CommandText += "   and " + sJOIN_KEY_RHS + " in " + ControlChars.CrLf;
														cmd.CommandText += "(select " + sRHS_KEY + " from " + sRHS_TABLE + ControlChars.CrLf;
														Security.Filter(cmd, sRHS_MODULE, "list");
														cmd.CommandText += ")" + ControlChars.CrLf;
													}
												}
												else if ( sRELATIONSHIP_TYPE == "one-to-many" )
												{
													cmd.CommandText += "   and " + sRHS_KEY + " in " + ControlChars.CrLf;
													cmd.CommandText += "(select " + sLHS_KEY + " from " + sLHS_TABLE + ControlChars.CrLf;
													Security.Filter(cmd, sLHS_MODULE, "list");
													cmd.CommandText += ")" + ControlChars.CrLf;
												}
											}
										}
										else
										{
											// 11/12/2009 Paul.  EMAIL_IMAGES is a special table that is related to EMAILS or KBDOCUMENTS. 
											if ( sTABLE_NAME == "EMAIL_IMAGES" )
											{
												// 11/12/2009 Paul.  There does not appear to be an easy way to filter the EMAIL_IMAGES table. 
												// For now, just return the EMAIL related images. 
												cmd.CommandText += "   and PARENT_ID in " + ControlChars.CrLf;
												cmd.CommandText += "(select ID from EMAILS" + ControlChars.CrLf;
												Security.Filter(cmd, "Emails", "list");
												cmd.CommandText += "union all" + ControlChars.CrLf;
												cmd.CommandText += "select ID from KBDOCUMENTS" + ControlChars.CrLf;
												Security.Filter(cmd, "KBDocuments", "list");
												cmd.CommandText += ")" + ControlChars.CrLf;
											}
											// 11/06/2009 Paul.  If the relationship is not in the RELATIONSHIPS table, then try and build it manually. 
											// 11/05/2009 Paul.  We cannot use the standard filter on the Teams table (or TeamNotices). 
											else if ( !Sql.IsEmptyString(sMODULE_NAME) && !sMODULE_NAME.StartsWith("Team") )
											{
												// 11/05/2009 Paul.  We could query the foreign key tables to perpare the filters, but that is slow. 
												string sMODULE_TABLE_NAME   = Sql.ToString(Context.Application["Modules." + sMODULE_NAME + ".TableName"]).ToUpper();
												if ( !Sql.IsEmptyString(sMODULE_TABLE_NAME) )
												{
													// 06/04/2011 Paul.  New function to get the singular name. 
													string sMODULE_FIELD_ID = Crm.Modules.SingularTableName(sMODULE_TABLE_NAME) + "_ID";
													
													cmd.CommandText += "   and " + sMODULE_FIELD_ID + " in " + ControlChars.CrLf;
													cmd.CommandText += "(select ID from " + sMODULE_TABLE_NAME + ControlChars.CrLf;
													Security.Filter(cmd, sMODULE_NAME, "list");
													cmd.CommandText += ")" + ControlChars.CrLf;
												}
											}
											// 11/05/2009 Paul.  We cannot use the standard filter on the Teams table. 
											if ( !Sql.IsEmptyString(sMODULE_NAME_RELATED) && !sMODULE_NAME_RELATED.StartsWith("Team") )
											{
												string sMODULE_TABLE_RELATED = Sql.ToString(Context.Application["Modules." + sMODULE_NAME_RELATED + ".TableName"]).ToUpper();
												if ( !Sql.IsEmptyString(sMODULE_TABLE_RELATED) )
												{
													// 06/04/2011 Paul.  New function to get the singular name. 
													string sMODULE_RELATED_ID = Crm.Modules.SingularTableName(sMODULE_TABLE_RELATED) + "_ID";
													
													// 11/05/2009 Paul.  Some tables use ASSIGNED_USER_ID as the relationship ID instead of the USER_ID. 
													if ( sMODULE_RELATED_ID == "USER_ID" && !Sql.IsEmptyString(sASSIGNED_FIELD_NAME) )
														sMODULE_RELATED_ID = sASSIGNED_FIELD_NAME;
													
													cmd.CommandText += "   and " + sMODULE_RELATED_ID + " in " + ControlChars.CrLf;
													cmd.CommandText += "(select ID from " + sMODULE_TABLE_RELATED + ControlChars.CrLf;
													Security.Filter(cmd, sMODULE_NAME_RELATED, "list");
													cmd.CommandText += ")" + ControlChars.CrLf;
												}
											}
										}
									}
									else
									{
										// 02/14/2010 Paul.  GetTable should only require read-only access. 
										// We were previously requiring Edit access, but that seems to be a high bar. 
										Security.Filter(cmd, sMODULE_NAME, "view");
									}
									if ( !Sql.IsEmptyString(sMODULE_FIELD_NAME) )
									{
										// 11/08/2009 Paul.  We need to combine the two module lists into a single list. 
										List<string> lstMODULES = new List<string>();
										List<string> lstAVAILABLE_MODULES = SplendidCache.AccessibleModules(Context, Security.USER_ID);
										if ( lstAVAILABLE_MODULES != null )
										{
											lstMODULES = lstAVAILABLE_MODULES;
										}
										// 11/14/2009 Paul.  Make sure to add Teams to the list of available modules. 
										if ( Crm.Config.enable_team_management() )
										{
											if ( !lstMODULES.Contains("Teams") )
												lstMODULES.Add("Teams");
											//if ( !lstMODULES.Contains("TeamNotices") )
											//	lstMODULES.Add("TeamNotices");
										}
										// 11/22/2009 Paul.  Simplify the logic by having a local list of system modules. 
										/*
										string[] arrSystemModules = new string[] { "ACL", "ACLActions", "ACLRoles", "Audit", "Config", "Currencies", "Dashlets"
										                                         , "DocumentRevisions", "DynamicButtons", "Export", "FieldValidators", "Import"
										                                         , "Merge", "Modules", "Offline", "Releases", "Roles", "SavedSearch", "Shortcuts"
										                                         , "Teams", "TeamNotices", "Terminology", "Users", "SystemSyncLog"
										                                         };
										*/
										string[] arrSystemModules = new string[] { "Currencies", "DocumentRevisions", "Releases", "Teams"
										                                         };
										foreach ( string sSystemModule in arrSystemModules )
											lstMODULES.Add(sSystemModule);
										
										if ( sTABLE_NAME == "MODULES" )
										{
											// 11/27/2009 Paul.  Don't filter the MODULES table. It can cause system tables to get deleted. 
											// 11/28/2009 Paul.  Keep the filter on the Modules table, but add the System Sync Tables to the list. 
											// We should make sure that the clients do not get module records for unnecessary or disabled modules. 
											Sql.AppendParameter(cmd, lstMODULES.ToArray(), sMODULE_FIELD_NAME);
										}
										else if ( nMODULE_SPECIFIC == 1 )
										{
											Sql.AppendParameter(cmd, lstMODULES.ToArray(), sMODULE_FIELD_NAME);
										}
										else if ( nMODULE_SPECIFIC == 2 )
										{
											Sql.AppendLikeParameters(cmd, lstMODULES.ToArray(), sMODULE_FIELD_NAME);
										}
										else if ( nMODULE_SPECIFIC == 3 )
										{
											cmd.CommandText += "   and ( 1 = 0" + ControlChars.CrLf;
											cmd.CommandText += "         or " + sMODULE_FIELD_NAME + " is null" + ControlChars.CrLf;
											// 11/02/2009 Paul.  There are a number of terms with undefined modules. 
											// ACL, ACLActions, Audit, Config, Dashlets, DocumentRevisions, Export, Merge, Roles, SavedSearch, Teams
											cmd.CommandText += "     ";
											Sql.AppendParameter(cmd, lstMODULES.ToArray(), sMODULE_FIELD_NAME, true);
											cmd.CommandText += "       )" + ControlChars.CrLf;
										}
										// 11/22/2009 Paul.  Make sure to only send the selected user language.  This will dramatically reduce the amount of data. 
										//if ( sTABLE_NAME == "TERMINOLOGY" || sTABLE_NAME == "TERMINOLOGY_HELP" )
										//{
										//	cmd.CommandText += "   and LANG in ('en-US', @LANG)" + ControlChars.CrLf;
										//	string sCULTURE  = Sql.ToString(Session["USER_SETTINGS/CULTURE" ]);
										//	Sql.AddParameter(cmd, "@LANG", sCULTURE);
										//}
									}
		
									if ( arrITEMS != null )
									{
										// 11/13/2009 Paul.  If a list of items is provided, then the max records field is ignored. 
										nSKIP = 0;
										nTOP = -1;
										Sql.AppendGuids(cmd, arrITEMS, "ID");
									}
									else if ( sTABLE_NAME == "IMAGES" )
									{
										// 02/14/2010 Paul.  There is no easy way to filter IMAGES table, so we are simply going to fetch 
										// images that the user has created.  Otherwise, images that are accessible to the user will 
										// need to be retrieved by ID.
										Sql.AppendParameter(cmd, Security.USER_ID, "CREATED_BY");
									}
									// 06/18/2011 Paul.  Tables that are filtered by user should have an explicit filter added. 
									if ( sASSIGNED_FIELD_NAME == "USER_ID" )
									{
										Sql.AppendParameter(cmd, Security.USER_ID, "USER_ID");
									}
									if ( !Sql.IsEmptyString(sFILTER) )
									{
										string sSQL_FILTER = ConvertODataFilter(sFILTER, cmd);
										cmd.CommandText += "   and (" + sSQL_FILTER + ")" + ControlChars.CrLf;;
									}
									if ( Sql.IsEmptyString(sORDER_BY) )
									{
										sORDER_BY = " order by " + sVIEW_NAME + ".DATE_MODIFIED_UTC" + ControlChars.CrLf;
									}
									else
									{
										// 06/18/2011 Paul.  Allow a comma in a sort expression. 
										r = new Regex(@"[^A-Za-z0-9_, ]");
										sORDER_BY = " order by " + r.Replace(sORDER_BY, "");
									}
									//cmd.CommandText += sORDER_BY;
#if DEBUG
									SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
#endif
									
									using ( DbDataAdapter da = dbf.CreateDataAdapter() )
									{
										((IDbDataAdapter)da).SelectCommand = cmd;
										// 11/08/2009 Paul.  The table name is required in order to serialize the DataTable. 
										dt = new DataTable(sTABLE_NAME);
										if ( nTOP > 0 )
										{
											if ( nSKIP > 0 )
											{
												int nCurrentPageIndex = nSKIP / nTOP;
												Sql.PageResults(cmd, sTABLE_NAME, sORDER_BY, nCurrentPageIndex, nTOP);
												da.Fill(dt);
											}
											else
											{
												cmd.CommandText += sORDER_BY;
												using ( DataSet ds = new DataSet() )
												{
													ds.Tables.Add(dt);
													da.Fill(ds, 0, nTOP, sTABLE_NAME);
												}
											}
										}
										else
										{
											cmd.CommandText += sORDER_BY;
											da.Fill(dt);
										}
										// 01/18/2010 Paul.  Apply ACL Field Security. 
										// 02/01/2010 Paul.  System tables may not have a valid Module name, so Field Security will not apply. 
										if ( SplendidInit.bEnableACLFieldSecurity && !Sql.IsEmptyString(sMODULE_NAME) )
										{
											bool bApplyACL = false;
											bool bASSIGNED_USER_ID_Exists = dt.Columns.Contains("ASSIGNED_USER_ID");
											foreach ( DataRow row in dt.Rows )
											{
												Guid gASSIGNED_USER_ID = Guid.Empty;
												if ( bASSIGNED_USER_ID_Exists )
													gASSIGNED_USER_ID = Sql.ToGuid(row["ASSIGNED_USER_ID"]);
												foreach ( DataColumn col in dt.Columns )
												{
													Security.ACL_FIELD_ACCESS acl = Security.GetUserFieldSecurity(sMODULE_NAME, col.ColumnName, gASSIGNED_USER_ID);
													if ( !acl.IsReadable() )
													{
														row[col.ColumnName] = DBNull.Value;
														bApplyACL = true;
													}
												}
											}
											if ( bApplyACL )
												dt.AcceptChanges();
										}
										if ( sTABLE_NAME == "USERS" )
										{
											// 11/12/2009 Paul.  For the USERS table, we are going to limit the data return to the client. 
											foreach ( DataRow row in dt.Rows )
											{
												if ( Sql.ToGuid(row["ID"]) != Security.USER_ID )
												{
													foreach ( DataColumn col in dt.Columns )
													{
														// 11/12/2009 Paul.  Allow auditing fields and basic user info. 
														if (  col.ColumnName != "ID"               
														   && col.ColumnName != "DELETED"          
														   && col.ColumnName != "CREATED_BY"       
														   && col.ColumnName != "DATE_ENTERED"     
														   && col.ColumnName != "MODIFIED_USER_ID" 
														   && col.ColumnName != "DATE_MODIFIED"    
														   && col.ColumnName != "DATE_MODIFIED_UTC"
														   && col.ColumnName != "USER_NAME"        
														   && col.ColumnName != "FIRST_NAME"       
														   && col.ColumnName != "LAST_NAME"        
														   && col.ColumnName != "REPORTS_TO_ID"    
														   && col.ColumnName != "EMAIL1"           
														   && col.ColumnName != "STATUS"           
														   && col.ColumnName != "IS_GROUP"         
														   && col.ColumnName != "PORTAL_ONLY"      
														   && col.ColumnName != "EMPLOYEE_STATUS"  
														   )
														{
															row[col.ColumnName] = DBNull.Value;
														}
													}
												}
											}
											dt.AcceptChanges();
										}
									}
								}
							}
							else
							{
								SplendidError.SystemError(new StackTrace(true).GetFrame(0), sTABLE_NAME + " cannot be accessed.");
							}
						}
					}
				}
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				throw;
			}
			return dt;
		}
		#endregion

		#region Update
		[OperationContract]
		// 03/13/2011 Paul.  Must use octet-stream instead of json, outherwise we get the following error. 
		// Incoming message for operation 'CreateRecord' (contract 'AddressService' with namespace 'http://tempuri.org/') contains an unrecognized http body format value 'Json'. 
		// The expected body format value is 'Raw'. This can be because a WebContentTypeMapper has not been configured on the binding. See the documentation of WebContentTypeMapper for more details.
		//xhr.setRequestHeader('content-type', 'application/octet-stream');
		public Guid UpdateModuleTable(Stream input)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			string sRequest = String.Empty;
			using ( StreamReader stmRequest = new StreamReader(input, System.Text.Encoding.UTF8) )
			{
				sRequest = stmRequest.ReadToEnd();
			}
			// http://weblogs.asp.net/hajan/archive/2010/07/23/javascriptserializer-dictionary-to-json-serialization-and-deserialization.aspx
			JavaScriptSerializer json = new JavaScriptSerializer();
			Dictionary<string, object> dict = json.Deserialize<Dictionary<string, object>>(sRequest);

			string sTableName = Sql.ToString(Request.QueryString["TableName"]);
			if ( Sql.IsEmptyString(sTableName) )
				throw(new Exception("The table name must be specified."));
			if ( !Security.IsAuthenticated() )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			// 08/22/2011 Paul.  Add admin control to REST API. 
			string sMODULE_NAME = Sql.ToString(Application["Modules." + sTableName + ".ModuleName"]);
			// 08/22/2011 Paul.  Not all tables will have a module name, such as relationship tables. 
			// Tables will get another security filter later in the code. 
			if ( !Sql.IsEmptyString(sMODULE_NAME) )
			{
				int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, "edit");
				if ( !Sql.ToBoolean(Application["Modules." + sMODULE_NAME + ".RestEnabled"]) || nACLACCESS < 0 )
				{
					L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
					throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
				}
			}
			
			Guid gID = UpdateTable(sTableName, dict);
			return gID;
		}

		[OperationContract]
		public Guid UpdateModule(Stream input)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			string sRequest = String.Empty;
			using ( StreamReader stmRequest = new StreamReader(input, System.Text.Encoding.UTF8) )
			{
				sRequest = stmRequest.ReadToEnd();
			}
			// http://weblogs.asp.net/hajan/archive/2010/07/23/javascriptserializer-dictionary-to-json-serialization-and-deserialization.aspx
			JavaScriptSerializer json = new JavaScriptSerializer();
			Dictionary<string, object> dict = json.Deserialize<Dictionary<string, object>>(sRequest);

			string sModuleName = Sql.ToString(Request.QueryString["ModuleName"]);
			if ( Sql.IsEmptyString(sModuleName) )
				throw(new Exception("The module name must be specified."));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			int nACLACCESS = Security.GetUserAccess(sModuleName, "edit");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + sModuleName + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			string sTableName = Sql.ToString(Application["Modules." + sModuleName + ".TableName"]);
			if ( Sql.IsEmptyString(sTableName) )
				throw(new Exception("Unknown module: " + sModuleName));
			
			Guid gID = UpdateTable(sTableName, dict);
			return gID;
		}

		private Guid UpdateTable(string sTABLE_NAME, Dictionary<string, object> dict)
		{
			HttpSessionState Session = HttpContext.Current.Session;
			Guid gID = Guid.Empty;
			try
			{
				DataTable dtUPDATE = new DataTable(sTABLE_NAME);
				foreach ( string sColumnName in dict.Keys )
				{
					dtUPDATE.Columns.Add(sColumnName);
				}
				DataRow row = dtUPDATE.NewRow();
				dtUPDATE.Rows.Add(row);
				foreach ( string sColumnName in dict.Keys )
				{
					// 09/09/2011 Paul.  Multi-selection list boxes will come in as an ArrayList. 
					if ( dict[sColumnName] is System.Collections.ArrayList )
					{
						System.Collections.ArrayList lst = dict[sColumnName] as System.Collections.ArrayList;
						XmlDocument xml = new XmlDocument();
						xml.AppendChild(xml.CreateXmlDeclaration("1.0", "UTF-8", null));
						xml.AppendChild(xml.CreateElement("Values"));
						if ( lst.Count > 0 )
						{
							foreach(string item in lst)
							{
								XmlNode xValue = xml.CreateElement("Value");
								xml.DocumentElement.AppendChild(xValue);
								xValue.InnerText = item;
							}
						}
						row[sColumnName] = xml.OuterXml;
					}
					else
					{
						row[sColumnName] = dict[sColumnName];
					}
				}
				//dtResults.Columns.Add("SPLENDID_SYNC_STATUS" , typeof(System.String));
				//dtResults.Columns.Add("SPLENDID_SYNC_MESSAGE", typeof(System.String));
				if ( Security.IsAuthenticated() )
				{
					string sCULTURE = Sql.ToString (Session["USER_SETTINGS/CULTURE"]);
					L10N   L10n     = new L10N(sCULTURE);
					Regex  r        = new Regex(@"[^A-Za-z0-9_]");
					sTABLE_NAME = r.Replace(sTABLE_NAME, "").ToUpper();
					
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						con.Open();
						// 06/03/2011 Paul.  Cache the Rest Table data. 
						// 11/26/2009 Paul.  System tables cannot be updated. 
						using ( DataTable dtSYNC_TABLES = SplendidCache.RestTables(sTABLE_NAME, true) )
						{
							string sSQL;
							if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count > 0 )
							{
								DataRow rowSYNC_TABLE = dtSYNC_TABLES.Rows[0];
								string sMODULE_NAME = Sql.ToString (rowSYNC_TABLE["MODULE_NAME"]);
								string sVIEW_NAME   = Sql.ToString (rowSYNC_TABLE["VIEW_NAME"  ]);
								bool   bHAS_CUSTOM  = Sql.ToBoolean(rowSYNC_TABLE["HAS_CUSTOM" ]);
								// 02/14/2010 Paul.  GetUserAccess requires a non-null sMODULE_NAME. 
								// Lets catch the exception here so that we can throw a meaningful error. 
								if ( Sql.IsEmptyString(sMODULE_NAME) )
								{
									throw(new Exception("sMODULE_NAME should not be empty for table " + sTABLE_NAME));
								}
								
								int nACLACCESS = BzwayCRM.Security.GetUserAccess(sMODULE_NAME, "edit");
								// 11/11/2009 Paul.  First check if the user has access to this module. 
								if ( nACLACCESS >= 0 )
								{
									bool      bRecordExists              = false;
									bool      bAccessAllowed             = false;
									Guid      gLOCAL_ASSIGNED_USER_ID    = Guid.Empty;
									DataRow   rowCurrent                 = null;
									DataTable dtCurrent                  = new DataTable();
									
									gID = Sql.ToGuid(row["ID"]);
									if ( !Sql.IsEmptyGuid(gID) )
									{
										sSQL = "select *"              + ControlChars.CrLf
										     + "  from " + sTABLE_NAME + ControlChars.CrLf
										     + " where 1 = 1"          + ControlChars.CrLf;
										using ( IDbCommand cmd = con.CreateCommand() )
										{
											cmd.CommandText = sSQL;
											Sql.AppendParameter(cmd, gID, "ID");
											using ( DbDataAdapter da = dbf.CreateDataAdapter() )
											{
												((IDbDataAdapter)da).SelectCommand = cmd;
												// 11/27/2009 Paul.  It may be useful to log the SQL during errors at this location. 
												try
												{
													da.Fill(dtCurrent);
												}
												catch
												{
													SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
													throw;
												}
												if ( dtCurrent.Rows.Count > 0 )
												{
													rowCurrent = dtCurrent.Rows[0];
													bRecordExists = true;
													// 01/18/2010 Paul.  Apply ACL Field Security. 
													if ( dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
													{
														gLOCAL_ASSIGNED_USER_ID = Sql.ToGuid(rowCurrent["ASSIGNED_USER_ID"]);
													}
												}
											}
										}
									}
									// 06/04/2011 Paul.  We are not ready to handle conflicts. 
									//if ( !bConflicted )
									{
										if ( bRecordExists )
										{
											sSQL = "select count(*)"       + ControlChars.CrLf
											     + "  from " + sTABLE_NAME + ControlChars.CrLf;
											using ( IDbCommand cmd = con.CreateCommand() )
											{
												cmd.CommandText = sSQL;
												Security.Filter(cmd, sMODULE_NAME, "edit");
												Sql.AppendParameter(cmd, gID, "ID");
												try
												{
													if ( Sql.ToInteger(cmd.ExecuteScalar()) > 0 )
													{
														if ( (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && Security.USER_ID == gLOCAL_ASSIGNED_USER_ID) || !dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
															bAccessAllowed = true;
													}
												}
												catch
												{
													SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
													throw;
												}
											}
										}
										if ( !bRecordExists || bAccessAllowed )
										{
											DataTable dtMetadata = SplendidCache.SqlColumns(sTABLE_NAME);
											using ( IDbTransaction trn = Sql.BeginTransaction(con) )
											{
												try
												{
													bool bEnableTeamManagement  = Crm.Config.enable_team_management();
													bool bRequireTeamManagement = Crm.Config.require_team_management();
													bool bRequireUserAssignment = Crm.Config.require_user_assignment();
													// 06/04/2011 Paul.  Unlike the Sync service, we want to use the stored procedures to update records. 
													IDbCommand cmdUpdate = SqlProcs.Factory(con, "sp" + sTABLE_NAME + "_Update");
													cmdUpdate.Transaction = trn;
													foreach(IDbDataParameter par in cmdUpdate.Parameters)
													{
														// 03/27/2010 Paul.  The ParameterName will start with @, so we need to remove it. 
														string sParameterName = Sql.ExtractDbName(cmdUpdate, par.ParameterName).ToUpper();
														if ( sParameterName == "TEAM_ID" && bEnableTeamManagement )
															par.Value = Sql.ToDBGuid(Security.TEAM_ID);  // 02/26/2011 Paul.  Make sure to convert Guid.Empty to DBNull. 
														else if ( sParameterName == "ASSIGNED_USER_ID" )
															par.Value = Sql.ToDBGuid(Security.USER_ID);  // 02/26/2011 Paul.  Make sure to convert Guid.Empty to DBNull. 
														else if ( sParameterName == "MODIFIED_USER_ID" )
															par.Value = Sql.ToDBGuid(Security.USER_ID);
														else
															par.Value = DBNull.Value;
													}
													if ( bRecordExists )
													{
														// 11/11/2009 Paul.  If the record already exists, then the current values are treated as default values. 
														foreach ( DataColumn col in rowCurrent.Table.Columns )
														{
															IDbDataParameter par = Sql.FindParameter(cmdUpdate, col.ColumnName);
															// 11/26/2009 Paul.  The UTC modified date should be set to Now. 
															if ( par != null && String.Compare(col.ColumnName, "DATE_MODIFIED_UTC", true) != 0 )
																par.Value = rowCurrent[col.ColumnName];
														}
													}
													
													foreach ( DataColumn col in row.Table.Columns )
													{
														// 01/18/2010 Paul.  Apply ACL Field Security. 
														// 02/01/2010 Paul.  System tables may not have a valid Module name, so Field Security will not apply. 
														bool bIsWriteable = true;
														if ( SplendidInit.bEnableACLFieldSecurity && !Sql.IsEmptyString(sMODULE_NAME) )
														{
															Security.ACL_FIELD_ACCESS acl = Security.GetUserFieldSecurity(sMODULE_NAME, col.ColumnName, Guid.Empty);
															bIsWriteable = acl.IsWriteable();
														}
														if ( bIsWriteable )
														{
															IDbDataParameter par = Sql.FindParameter(cmdUpdate, col.ColumnName);
															// 11/26/2009 Paul.  The UTC modified date should be set to Now. 
															if ( par != null )
															{
																switch ( par.DbType )
																{
																	// 10/08/2011 Paul.  We must use Sql.ToDBDateTime, otherwise we get a an error whe DateTime.MinValue is used. 
																	// SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
																	case DbType.Date                 :  par.Value = Sql.ToDBDateTime(FromJsonDate(Sql.ToString(row[col.ColumnName])));  break;
																	case DbType.DateTime             :  par.Value = Sql.ToDBDateTime(FromJsonDate(Sql.ToString(row[col.ColumnName])));  break;
																	case DbType.Int16                :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.Int32                :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.Int64                :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.UInt16               :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.UInt32               :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.UInt64               :  par.Value = Sql.ToDBInteger(row[col.ColumnName]);  break;
																	case DbType.Single               :  par.Value = Sql.ToDBFloat  (row[col.ColumnName]);  break;
																	case DbType.Double               :  par.Value = Sql.ToDBFloat  (row[col.ColumnName]);  break;
																	case DbType.Decimal              :  par.Value = Sql.ToDBDecimal(row[col.ColumnName]);  break;
																	case DbType.Currency             :  par.Value = Sql.ToDBDecimal(row[col.ColumnName]);  break;
																	case DbType.Boolean              :  par.Value = Sql.ToDBBoolean(row[col.ColumnName]);  break;
																	case DbType.Guid                 :  par.Value = Sql.ToDBGuid   (row[col.ColumnName]);  break;
																	case DbType.String               :  par.Value = Sql.ToDBString (row[col.ColumnName]);  break;
																	case DbType.StringFixedLength    :  par.Value = Sql.ToDBString (row[col.ColumnName]);  break;
																	case DbType.AnsiString           :  par.Value = Sql.ToDBString (row[col.ColumnName]);  break;
																	case DbType.AnsiStringFixedLength:  par.Value = Sql.ToDBString (row[col.ColumnName]);  break;
																}
															}
														}
													}
													cmdUpdate.ExecuteScalar();
													IDbDataParameter parID = Sql.FindParameter(cmdUpdate, "@ID");
													if ( parID != null )
													{
														gID = Sql.ToGuid(parID.Value);
														if ( bHAS_CUSTOM )
														{
															DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sTABLE_NAME);
															SplendidDynamic.UpdateCustomFields(row, trn, gID, sTABLE_NAME, dtCustomFields);
														}
													}
													trn.Commit();
												}
												catch
												{
													trn.Rollback();
													throw;
												}
											}
										}
										else
										{
											//DataRow rowError = dtResults.NewRow();
											//dtResults.Rows.Add(rowError);
											//rowError["ID"                   ] = gID;
											//rowError["SPLENDID_SYNC_STATUS" ] = "Access Denied";
											//rowError["SPLENDID_SYNC_MESSAGE"] = L10n.Term("ACL.LBL_NO_ACCESS");
											throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
										}
									}
								}
								else
								{
									throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
								}
							}
						}
					}
				}
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				throw;
			}
			return gID;
		}
		#endregion

		#region Delete
		// 3.2 Method Tunneling through POST. 
		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public void DeleteModuleItem(string ModuleName, Guid ID)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			if ( Sql.IsEmptyString(ModuleName) )
				throw(new Exception("The module name must be specified."));
			string sTABLE_NAME = Sql.ToString(Application["Modules." + ModuleName + ".TableName"]);
			if ( Sql.IsEmptyString(sTABLE_NAME) )
				throw(new Exception("Unknown module: " + ModuleName));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			int nACLACCESS = Security.GetUserAccess(ModuleName, "delete");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + ModuleName + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			DeleteTableItem(sTABLE_NAME, ID);
		}

		[OperationContract]
		[WebInvoke(Method="POST", BodyStyle=WebMessageBodyStyle.WrappedRequest, RequestFormat=WebMessageFormat.Json, ResponseFormat=WebMessageFormat.Json)]
		public void DeleteRelatedItem(string ModuleName, Guid ID, string RelatedModule, Guid RelatedID)
		{
			HttpApplicationState Application = HttpContext.Current.Application;
			HttpRequest          Request     = HttpContext.Current.Request    ;
			
			if ( Sql.IsEmptyString(ModuleName) )
				throw(new Exception("The module name must be specified."));
			string sTABLE_NAME = Sql.ToString(Application["Modules." + ModuleName + ".TableName"]);
			if ( Sql.IsEmptyString(sTABLE_NAME) )
				throw(new Exception("Unknown module: " + ModuleName));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			int nACLACCESS = Security.GetUserAccess(ModuleName, "edit");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + ModuleName + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}
			
			if ( Sql.IsEmptyString(RelatedModule) )
				throw(new Exception("The related module name must be specified."));
			string sRELATED_TABLE = Sql.ToString(Application["Modules." + RelatedModule + ".TableName"]);
			if ( Sql.IsEmptyString(sRELATED_TABLE) )
				throw(new Exception("Unknown module: " + RelatedModule));
			// 08/22/2011 Paul.  Add admin control to REST API. 
			nACLACCESS = Security.GetUserAccess(RelatedModule, "edit");
			if ( !Security.IsAuthenticated() || !Sql.ToBoolean(Application["Modules." + RelatedModule + ".RestEnabled"]) || nACLACCESS < 0 )
			{
				L10N L10n = new L10N(Sql.ToString(HttpContext.Current.Session["USER_SETTINGS/CULTURE"]));
				throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
			}

			string sMODULE_FIELD_NAME  = Crm.Modules.SingularTableName(sTABLE_NAME   ) + "_ID";
			string sRELATED_FIELD_NAME = Crm.Modules.SingularTableName(sRELATED_TABLE) + "_ID";
			
			string sRELATIONSHIP_TABLE = sTABLE_NAME + "_" + sRELATED_TABLE;
			DataTable dtSYNC_TABLES = SplendidCache.RestTables(sRELATIONSHIP_TABLE, true);
			if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count == 0 )
			{
				sRELATIONSHIP_TABLE = sRELATED_TABLE + "_" + sTABLE_NAME;
			}
			DeleteRelatedItem(sTABLE_NAME, sRELATIONSHIP_TABLE, sMODULE_FIELD_NAME, ID, sRELATED_FIELD_NAME, RelatedID);
		}

		private void DeleteTableItem(string sTABLE_NAME, Guid gID)
		{
			HttpSessionState     Session     = HttpContext.Current.Session    ;
			try
			{
				if ( Security.IsAuthenticated() )
				{
					string sCULTURE = Sql.ToString (Session["USER_SETTINGS/CULTURE"]);
					L10N   L10n     = new L10N(sCULTURE);
					Regex  r        = new Regex(@"[^A-Za-z0-9_]");
					sTABLE_NAME = r.Replace(sTABLE_NAME, "").ToUpper();
					
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						con.Open();
						// 06/03/2011 Paul.  Cache the Rest Table data. 
						// 11/26/2009 Paul.  System tables cannot be updated. 
						using ( DataTable dtSYNC_TABLES = SplendidCache.RestTables(sTABLE_NAME, true) )
						{
							string sSQL;
							if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count > 0 )
							{
								DataRow rowSYNC_TABLE = dtSYNC_TABLES.Rows[0];
								string sMODULE_NAME = Sql.ToString (rowSYNC_TABLE["MODULE_NAME"]);
								string sVIEW_NAME   = Sql.ToString (rowSYNC_TABLE["VIEW_NAME"  ]);
								if ( Sql.IsEmptyString(sMODULE_NAME) )
								{
									throw(new Exception("sMODULE_NAME should not be empty for table " + sTABLE_NAME));
								}
								
								int nACLACCESS = BzwayCRM.Security.GetUserAccess(sMODULE_NAME, "edit");
								// 11/11/2009 Paul.  First check if the user has access to this module. 
								if ( nACLACCESS >= 0 )
								{
									bool      bRecordExists              = false;
									bool      bAccessAllowed             = false;
									Guid      gLOCAL_ASSIGNED_USER_ID    = Guid.Empty;
									DataRow   rowCurrent                 = null;
									DataTable dtCurrent                  = new DataTable();
									sSQL = "select *"              + ControlChars.CrLf
									     + "  from " + sTABLE_NAME + ControlChars.CrLf
									     + " where 1 = 1"          + ControlChars.CrLf;
									using ( IDbCommand cmd = con.CreateCommand() )
									{
										cmd.CommandText = sSQL;
										Sql.AppendParameter(cmd, gID, "ID");
										using ( DbDataAdapter da = dbf.CreateDataAdapter() )
										{
											((IDbDataAdapter)da).SelectCommand = cmd;
											// 11/27/2009 Paul.  It may be useful to log the SQL during errors at this location. 
											try
											{
												da.Fill(dtCurrent);
											}
											catch
											{
												SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
												throw;
											}
											if ( dtCurrent.Rows.Count > 0 )
											{
												rowCurrent = dtCurrent.Rows[0];
												bRecordExists = true;
												// 01/18/2010 Paul.  Apply ACL Field Security. 
												if ( dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
												{
													gLOCAL_ASSIGNED_USER_ID = Sql.ToGuid(rowCurrent["ASSIGNED_USER_ID"]);
												}
											}
										}
									}
									// 06/04/2011 Paul.  We are not ready to handle conflicts. 
									//if ( !bConflicted )
									{
										if ( bRecordExists )
										{
											sSQL = "select count(*)"       + ControlChars.CrLf
											     + "  from " + sTABLE_NAME + ControlChars.CrLf;
											using ( IDbCommand cmd = con.CreateCommand() )
											{
												cmd.CommandText = sSQL;
												Security.Filter(cmd, sMODULE_NAME, "delete");
												Sql.AppendParameter(cmd, gID, "ID");
												try
												{
													if ( Sql.ToInteger(cmd.ExecuteScalar()) > 0 )
													{
														if ( (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && Security.USER_ID == gLOCAL_ASSIGNED_USER_ID) || !dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
															bAccessAllowed = true;
													}
												}
												catch
												{
													SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
													throw;
												}
											}
										}
										if ( bAccessAllowed )
										{
											using ( IDbTransaction trn = Sql.BeginTransaction(con) )
											{
												try
												{
													IDbCommand cmdDelete = SqlProcs.Factory(con, "sp" + sTABLE_NAME + "_Delete");
													cmdDelete.Transaction = trn;
													foreach(IDbDataParameter par in cmdDelete.Parameters)
													{
														string sParameterName = Sql.ExtractDbName(cmdDelete, par.ParameterName).ToUpper();
														if ( sParameterName == "ID" )
															par.Value = gID;
														else if ( sParameterName == "MODIFIED_USER_ID" )
															par.Value = Sql.ToDBGuid(Security.USER_ID);
														else
															par.Value = DBNull.Value;
													}
													
													cmdDelete.ExecuteScalar();
													trn.Commit();
												}
												catch
												{
													trn.Rollback();
													throw;
												}
											}
										}
										else
										{
											throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
										}
									}
								}
								else
								{
									throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
								}
							}
						}
					}
				}
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				throw;
			}
		}

		private void DeleteRelatedItem(string sTABLE_NAME, string sRELATIONSHIP_TABLE, string sMODULE_FIELD_NAME, Guid gID, string sRELATED_FIELD_NAME, Guid gRELATED_ID)
		{
			HttpSessionState     Session     = HttpContext.Current.Session    ;
			try
			{
				if ( Security.IsAuthenticated() )
				{
					string sCULTURE = Sql.ToString (Session["USER_SETTINGS/CULTURE"]);
					L10N   L10n     = new L10N(sCULTURE);
					Regex  r        = new Regex(@"[^A-Za-z0-9_]");
					sTABLE_NAME = r.Replace(sTABLE_NAME, "").ToUpper();
					
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						con.Open();
						// 06/03/2011 Paul.  Cache the Rest Table data. 
						// 11/26/2009 Paul.  System tables cannot be updated. 
						// 06/04/2011 Paul.  For relationships, we first need to check the access rights of the parent record. 
						using ( DataTable dtSYNC_TABLES = SplendidCache.RestTables(sTABLE_NAME, true) )
						{
							string sSQL;
							if ( dtSYNC_TABLES != null && dtSYNC_TABLES.Rows.Count > 0 )
							{
								DataRow rowSYNC_TABLE = dtSYNC_TABLES.Rows[0];
								string sMODULE_NAME = Sql.ToString (rowSYNC_TABLE["MODULE_NAME"]);
								string sVIEW_NAME   = Sql.ToString (rowSYNC_TABLE["VIEW_NAME"  ]);
								if ( Sql.IsEmptyString(sMODULE_NAME) )
								{
									throw(new Exception("sMODULE_NAME should not be empty for table " + sTABLE_NAME));
								}
								
								int nACLACCESS = BzwayCRM.Security.GetUserAccess(sMODULE_NAME, "edit");
								// 11/11/2009 Paul.  First check if the user has access to this module. 
								if ( nACLACCESS >= 0 )
								{
									bool      bRecordExists              = false;
									bool      bAccessAllowed             = false;
									Guid      gLOCAL_ASSIGNED_USER_ID    = Guid.Empty;
									DataRow   rowCurrent                 = null;
									DataTable dtCurrent                  = new DataTable();
									sSQL = "select *"              + ControlChars.CrLf
									     + "  from " + sTABLE_NAME + ControlChars.CrLf
									     + " where DELETED = 0"    + ControlChars.CrLf;
									using ( IDbCommand cmd = con.CreateCommand() )
									{
										cmd.CommandText = sSQL;
										Sql.AppendParameter(cmd, gID, "ID");
										using ( DbDataAdapter da = dbf.CreateDataAdapter() )
										{
											((IDbDataAdapter)da).SelectCommand = cmd;
											// 11/27/2009 Paul.  It may be useful to log the SQL during errors at this location. 
											try
											{
												da.Fill(dtCurrent);
											}
											catch
											{
												SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
												throw;
											}
											if ( dtCurrent.Rows.Count > 0 )
											{
												rowCurrent = dtCurrent.Rows[0];
												bRecordExists = true;
												// 01/18/2010 Paul.  Apply ACL Field Security. 
												if ( dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
												{
													gLOCAL_ASSIGNED_USER_ID = Sql.ToGuid(rowCurrent["ASSIGNED_USER_ID"]);
												}
											}
										}
									}
									// 06/04/2011 Paul.  We are not ready to handle conflicts. 
									//if ( !bConflicted )
									{
										if ( bRecordExists )
										{
											sSQL = "select count(*)"       + ControlChars.CrLf
											     + "  from " + sTABLE_NAME + ControlChars.CrLf;
											using ( IDbCommand cmd = con.CreateCommand() )
											{
												cmd.CommandText = sSQL;
												Security.Filter(cmd, sMODULE_NAME, "delete");
												Sql.AppendParameter(cmd, gID, "ID");
												try
												{
													if ( Sql.ToInteger(cmd.ExecuteScalar()) > 0 )
													{
														if ( (nACLACCESS > ACL_ACCESS.OWNER) || (nACLACCESS == ACL_ACCESS.OWNER && Security.USER_ID == gLOCAL_ASSIGNED_USER_ID) || !dtCurrent.Columns.Contains("ASSIGNED_USER_ID") )
															bAccessAllowed = true;
													}
												}
												catch
												{
													SplendidError.SystemError(new StackTrace(true).GetFrame(0), Sql.ExpandParameters(cmd));
													throw;
												}
											}
										}
										if ( bAccessAllowed )
										{
											using ( DataTable dtRELATIONSHIP_TABLES = SplendidCache.RestTables(sRELATIONSHIP_TABLE, true) )
											{
												if ( dtRELATIONSHIP_TABLES != null && dtRELATIONSHIP_TABLES.Rows.Count > 0 )
												{
													rowSYNC_TABLE = dtRELATIONSHIP_TABLES.Rows[0];
													using ( IDbTransaction trn = Sql.BeginTransaction(con) )
													{
														try
														{
															IDbCommand cmdDelete = SqlProcs.Factory(con, "sp" + sRELATIONSHIP_TABLE + "_Delete");
															cmdDelete.Transaction = trn;
															foreach(IDbDataParameter par in cmdDelete.Parameters)
															{
																string sParameterName = Sql.ExtractDbName(cmdDelete, par.ParameterName).ToUpper();
																if ( sParameterName == sMODULE_FIELD_NAME )
																	par.Value = gID;
																else if ( sParameterName == sRELATED_FIELD_NAME )
																	par.Value = gRELATED_ID;
																else if ( sParameterName == "MODIFIED_USER_ID" )
																	par.Value = Sql.ToDBGuid(Security.USER_ID);
																else
																	par.Value = DBNull.Value;
															}
															
															cmdDelete.ExecuteScalar();
															trn.Commit();
														}
														catch
														{
															trn.Rollback();
															throw;
														}
													}
												}
											}
										}
										else
										{
											throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
										}
									}
								}
								else
								{
									throw(new Exception(L10n.Term("ACL.LBL_NO_ACCESS")));
								}
							}
						}
					}
				}
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				throw;
			}
		}
		#endregion
	}
}

